from collections.abc import AsyncGenerator
from typing import Generator, Optional
from uuid import UUID, uuid4
from fastapi import Depends
from fastapi_users.db import SQLAlchemyBaseUserTableUUID, SQLAlchemyUserDatabase
from loguru import logger
from sqlalchemy import Column, Integer, String, LargeBinary, DateTime, ForeignKey, Text, create_engine, types, inspect, text, JSON, Boolean, select, Index
from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker, AsyncSession
from sqlalchemy.orm import DeclarativeBase, relationship, foreign
from datetime import datetime, timedelta
from config import DATABASE_URL, DATABASETB_URL
from cryptography.fernet import Fernet
from app.utils.security import encrypt_text, decrypt_text
import pandas as pd
import json
import urllib.parse  # 添加urllib.parse模块导入
from passlib.context import CryptContext
from sqlalchemy.dialects.mysql import LONGTEXT, MEDIUMTEXT


# 创建异步引擎
engine = create_async_engine(
    DATABASE_URL,
    echo=False,
    pool_pre_ping=True,
    pool_recycle=3600
)
sync_engine = create_engine(
    DATABASETB_URL,  # 确保使用正确的连接字符串
    echo=False,
    pool_pre_ping=True,
    pool_recycle=3600
)

# 创建异步会话工厂
async_session_maker = async_sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)


class Base(DeclarativeBase):
    pass


class User(SQLAlchemyBaseUserTableUUID, Base):
    __tablename__ = "dk_user"
    
    email = Column(String(320), nullable=True)  # 重写 email 字段，移除唯一索引
    username = Column(String(50), unique=True, nullable=False, comment="用户名")
    phone = Column(String(50), nullable=True, comment="手机号")  # 移除唯一索引，设置为可选
    nickname = Column(String(50), nullable=True, comment="用户昵称")
    avatar = Column(String(255), nullable=True, comment="用户头像URL")
    gender = Column(Integer, nullable=True, default=0, comment="性别 0=未知，1=男，2=女")
    birthday = Column(DateTime, nullable=True, comment="出生日期")
    department = Column(String(100), nullable=True, comment="部门")
    position = Column(String(100), nullable=True, comment="职位")
    last_login_time = Column(DateTime, nullable=True, comment="最后登录时间")
    last_login_ip = Column(String(50), nullable=True, comment="最后登录IP")
    status = Column(Integer, default=1, nullable=False, comment="状态 0=禁用，1=启用")
    created_at = Column(DateTime, default=datetime.now)
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)
    usermsgs = relationship(
        "UserMsg", back_populates="user", order_by="UserMsg.timestamp", 
        primaryjoin="User.id == foreign(UserMsg.user_id)")
    usersessions = relationship(
        "UserSession", back_populates="user", order_by="UserSession.upload_time", 
        primaryjoin="User.id == foreign(UserSession.user_id)")

    def dict(self):
        return {
            "id": str(self.id),
            "username": self.username,
            "email": self.email,
            "phone": self.phone,
            "is_active": self.is_active,
            "is_superuser": self.is_superuser,
            "is_verified": self.is_verified,
            "nickname": self.nickname,
            "avatar": self.avatar,
            "gender": self.gender,
            "birthday": self.birthday.isoformat() if self.birthday else None,
            "department": self.department,
            "position": self.position,
            "last_login_time": self.last_login_time.isoformat() if self.last_login_time else None,
            "status": self.status,
            "created_at": self.created_at.isoformat() if self.created_at else None,
            "updated_at": self.updated_at.isoformat() if self.updated_at else None
        }

    def verify_password(self, password: str) -> bool:
        """
        验证密码
        
        Args:
            password: 明文密码
            
        Returns:
            bool: 密码是否正确
        """
        pwd_context = CryptContext(schemes=["bcrypt"], deprecated="auto")
        return pwd_context.verify(password, self.hashed_password)


class UserMsg(Base):
    __tablename__ = "dk_user_msg"

    id = Column(String(36), primary_key=True, default=lambda: str(uuid4()))
    user_id = Column(String(36), nullable=False)
    session_id = Column(String(36), nullable=False)

    message = Column(MEDIUMTEXT, nullable=True, default='') 
    message_type = Column(String(10), nullable=False)  # 'user' or 'system'
    timestamp = Column(DateTime, default=datetime.now)
    message_remark = Column(Text, nullable=True, default='')  # excel存储行信息
    message_sourceid = Column(String(36), nullable=True)
    message_model = Column(String(50), nullable=True, default='gpt-4')  # 新增字段，存储大模型类型
    message_sql = Column(Text, nullable=True, default='')  # 新增字段，存储生成的SQL语句
    message_data = Column(MEDIUMTEXT, nullable=True, default='')  # 新增字段，存储SQL执行结果数据
    message_intent = Column(String(50), nullable=True, default='')  # 新增字段，存储用户意图
    message_confirm = Column(Text, nullable=True, default='')  # 新增字段，存储需求确认信息
    message_suggest = Column(Text, nullable=True, default='')  # 新增字段，存储推荐关联问题
    chart_option = Column(Text, nullable=True, default='')  # 新增字段，存储图表配置JSON
    user = relationship("User", back_populates="usermsgs", foreign_keys=[user_id], 
                        primaryjoin="foreign(UserMsg.user_id) == User.id")
    session = relationship("UserSession", back_populates="messages", foreign_keys=[session_id], 
                           primaryjoin="foreign(UserMsg.session_id) == UserSession.id")

    def dict(self):
        return {
            "id": self.id,
            "user_id": self.user_id,
            "session_id": self.session_id,
            "message": self.message,
            "message_type": self.message_type,
            "message_model": self.message_model,
            "message_sql": self.message_sql,
            "message_intent": self.message_intent,
            "message_confirm": self.message_confirm,
            "message_suggest": self.message_suggest,
            "chart_option": self.chart_option,
            "timestamp": self.timestamp.isoformat()
        }


class UserSession(Base):
    __tablename__ = "dk_user_session"

    id = Column(String(36), primary_key=True, default=lambda: str(uuid4()))
    user_id = Column(String(36), nullable=False)
    file_name = Column(String(255), nullable=False)
    file_content = Column(Text, nullable=True, default='')
    session_title = Column(String(255), nullable=True, default='')  # 增加长度以容纳路径
    file_url = Column(String(255), nullable=False, default='')  # 增加长度以容纳路径
    upload_time = Column(DateTime, default=datetime.now)
    analysis_status = Column(String(255), nullable=True, default='')  # 新增字段
    analysis_results = Column(Text, nullable=True)  # 新增字段
    session_remark = Column(Text, nullable=True, default='')  # 新增字段,excel存储行信息
    dataset_ids = Column(Text, nullable=True, default='', comment='关联的数据集ID列表，以逗号分隔')  # 新增字段，存储关联的数据集ID
    user = relationship("User", back_populates="usersessions", foreign_keys=[user_id], 
                        primaryjoin="foreign(UserSession.user_id) == User.id")
    messages = relationship(
        "UserMsg", back_populates="session", order_by="UserMsg.timestamp", 
        primaryjoin="UserSession.id == foreign(UserMsg.session_id)")

    def get_dataset_ids(self):
        """获取关联的数据集ID列表"""
        if not self.dataset_ids:
            return []
        return self.dataset_ids.split(',')
        
    def set_dataset_ids(self, id_list):
        """设置关联的数据集ID列表"""
        if not id_list:
            self.dataset_ids = ''
        else:
            self.dataset_ids = ','.join(id_list)


class AnalysisReport(Base):
    __tablename__ = 'dk_analysis_reports'
    id = Column(String(36), primary_key=True, default=lambda: str(uuid4()))
    dataset_id = Column(String(255), nullable=False)
    creator_id = Column(String(36), nullable=True, comment="创建人ID")
    report = Column(Text, nullable=False, default='')
    report_satus = Column(String(255), nullable=False, default='')
    report_title = Column(String(255), nullable=False, default='')
    report_dimension = Column(String(255), nullable=False)  # 新增字段：分析维度
    report_industry = Column(String(255), nullable=True)
    report_description = Column(String(255), nullable=True)
    report_questions = Column(String(255), nullable=True)
    report_methods = Column(String(255), nullable=True)
    analysis_outline = Column(Text, nullable=True, comment="分析大纲信息")
    created_at = Column(DateTime, default=datetime.now)  # 新增字段：创建时间
    charts_data = Column(Text, nullable=True)  # 新增字段：图表数据
    data_filters = Column(Text, nullable=True, comment="数据集过滤条件，JSON格式")  # 新增字段：数据过滤条件
    
    # 添加会话关系
    dataset = relationship("DataSet", backref="reports", foreign_keys=[dataset_id], 
                           primaryjoin="foreign(AnalysisReport.dataset_id) == DataSet.id")
    
    # 添加创建者关系
    creator = relationship("User", backref="reports", foreign_keys=[creator_id],
                          primaryjoin="foreign(AnalysisReport.creator_id) == User.id")
    



class SmsCode(Base):
    __tablename__ = "dk_sms_code"

    id = Column(String(36), primary_key=True, default=lambda: str(uuid4()))
    phone = Column(String(50), unique=True, nullable=False)
    code = Column(String(6), nullable=False)
    created_at = Column(DateTime, default=datetime.now)
    expired_at = Column(
        DateTime, default=lambda: datetime.now() + timedelta(minutes=5))

    def dict(self):
        return {
            "id": self.id,
            "phone": self.phone,
            "code": self.code,
            "created_at": self.created_at.isoformat(),
            "expired_at": self.expired_at.isoformat()
        }


class AnalysisDimension(Base):
    __tablename__ = "dk_analysis_dimensions"

    id = Column(String(36), primary_key=True, default=lambda: str(uuid4()))
    dataset_id = Column(String(36), nullable=False, comment="关联的数据集ID")
    report_id = Column(String(36), nullable=True, comment="关联的报告ID，可为空")
    creator_id = Column(String(36), nullable=True, comment="创建人ID")
    dimension_type = Column(String(50), nullable=True, default="user_dimension", comment="维度类型：user=用户生成的分析维度，report=报告章节")
    dimension_name = Column(String(255), nullable=False, comment="分析维度名称")
    dimension_description = Column(Text, nullable=True, comment="分析维度描述")
    analysis_goal = Column(Text, nullable=True, comment="分析目标")
    analysis_method = Column(String(255), nullable=True, comment="分析方法")
    analysis_sql = Column(Text, nullable=True, comment="分析SQL语句")
    answerable_questions = Column(Text, nullable=True, comment="能回答的问题")
    chart_suggestions = Column(Text, nullable=True, comment="图表建议")
    recommended_charts = Column(Text, nullable=True, comment="推荐图表配置项")
    is_locked = Column(Integer, nullable=False, default=0, comment="是否锁定，0=非锁定，1=锁定")
    json_data = Column(MEDIUMTEXT, nullable=True, comment="存储获取的JSON数据")
    analysis_result = Column(Text, nullable=True, comment="存储分析结果")
    created_at = Column(DateTime, default=datetime.now)
    dimension_order = Column(Integer, nullable=True, comment="维度排序")
    status = Column(String(50), nullable=False, default="pending", comment="执行状态：pending=未开始, in_progress=进行中, completed=已完成, failed=失败")
    # 关联关系
    dataset = relationship("DataSet", backref="dimensions", foreign_keys=[dataset_id], 
                           primaryjoin="foreign(AnalysisDimension.dataset_id) == DataSet.id")
    report = relationship("AnalysisReport", backref="dimensions", foreign_keys=[report_id],
                          primaryjoin="foreign(AnalysisDimension.report_id) == AnalysisReport.id")
    creator = relationship("User", backref="dimensions", foreign_keys=[creator_id],
                          primaryjoin="foreign(AnalysisDimension.creator_id) == User.id")
    
    def dict(self):
        return {
            "id": self.id,
            "dataset_id": self.dataset_id,
            "report_id": self.report_id,
            "creator_id": self.creator_id,
            "dimension_type": self.dimension_type,
            "dimension_name": self.dimension_name,
            "dimension_description": self.dimension_description,
            "analysis_goal": self.analysis_goal,
            "analysis_method": self.analysis_method,
            "analysis_sql": self.analysis_sql,
            "answerable_questions": self.answerable_questions,
            "chart_suggestions": self.chart_suggestions,
            "recommended_charts": self.recommended_charts,
            "is_locked": bool(self.is_locked),
            "json_data": self.json_data,
            "analysis_result": self.analysis_result,
            "created_at": self.created_at.isoformat() if self.created_at else None,
            "dimension_order": self.dimension_order,
            "status": self.status
        }


# 角色管理相关模型
class Role(Base):
    __tablename__ = "dk_roles"
    
    id = Column(String(36), primary_key=True, default=lambda: str(uuid4()))
    name = Column(String(50), unique=True, nullable=False, comment="角色名称")
    description = Column(Text, nullable=True, comment="角色描述")
    is_system = Column(Integer, nullable=False, default=0, comment="是否系统内置角色 0=否，1=是")
    created_at = Column(DateTime, default=datetime.now)
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)
    
    # 关联关系
    users = relationship("UserRole", back_populates="role", 
                         primaryjoin="Role.id == foreign(UserRole.role_id)")
    dataset_accesses = relationship(
        "RoleDatasetAccess", 
        back_populates="role", 
        cascade="all, delete-orphan",
        primaryjoin="Role.id == foreign(RoleDatasetAccess.role_id)"
    )
    
    def dict(self):
        return {
            "id": self.id,
            "name": self.name,
            "description": self.description,
            "is_system": bool(self.is_system),
            "created_at": self.created_at.isoformat() if self.created_at else None,
            "updated_at": self.updated_at.isoformat() if self.updated_at else None
        }

class UserRole(Base):
    __tablename__ = "dk_user_roles"
    
    id = Column(String(36), primary_key=True, default=lambda: str(uuid4()))
    user_id = Column(String(36), nullable=False)
    role_id = Column(String(36), nullable=False)
    created_at = Column(DateTime, default=datetime.now)
    
    # 关联关系
    user = relationship("User", backref="user_roles", foreign_keys=[user_id], 
                        primaryjoin="foreign(UserRole.user_id) == User.id")
    role = relationship("Role", back_populates="users", foreign_keys=[role_id], 
                        primaryjoin="foreign(UserRole.role_id) == Role.id")
    
    def dict(self):
        return {
            "id": self.id,
            "user_id": self.user_id,
            "role_id": self.role_id,
            "created_at": self.created_at.isoformat() if self.created_at else None
        }

# 操作日志模型
class OperationLog(Base):
    __tablename__ = "dk_operation_logs"
    
    id = Column(String(36), primary_key=True, default=lambda: str(uuid4()))
    user_id = Column(String(36), nullable=True)
    operation_type = Column(String(50), nullable=False, comment="操作类型")
    operation_content = Column(Text, nullable=False, comment="操作内容")
    operation_result = Column(Integer, nullable=False, default=1, comment="操作结果 0=失败，1=成功")
    operation_ip = Column(String(50), nullable=True, comment="操作IP")
    operation_time = Column(DateTime, default=datetime.now)
    
    # 关联关系
    user = relationship("User", backref="operation_logs", foreign_keys=[user_id], 
                        primaryjoin="foreign(OperationLog.user_id) == User.id")
    
    def dict(self):
        return {
            "id": self.id,
            "user_id": self.user_id,
            "operation_type": self.operation_type,
            "operation_content": self.operation_content,
            "operation_result": bool(self.operation_result),
            "operation_ip": self.operation_ip,
            "operation_time": self.operation_time.isoformat() if self.operation_time else None
        }

# AI模型调用日志
class ModelCallLog(Base):
    __tablename__ = "dk_model_call_logs"
    
    id = Column(String(36), primary_key=True)
    user_id = Column(String(36), nullable=True, comment="调用用户ID")
    session_id = Column(String(36), nullable=True, comment="关联会话ID")
    model_name = Column(String(50), nullable=False, comment="调用的模型名称")
    prompt_type = Column(String(50), nullable=True, comment="提示词类型")
    prompt_content = Column(MEDIUMTEXT, nullable=True, comment="提示词内容")
    response_content = Column(MEDIUMTEXT, nullable=True, comment="响应内容")
    tokens_prompt = Column(Integer, nullable=True, comment="提示词token数量")
    tokens_completion = Column(Integer, nullable=True, comment="完成token数量")
    tokens_total = Column(Integer, nullable=True, comment="总token数量")
    duration_ms = Column(Integer, nullable=True, comment="调用耗时(毫秒)")
    status_code = Column(Integer, nullable=True, comment="调用状态码")
    error_message = Column(Text, nullable=True, comment="错误信息")
    created_at = Column(DateTime, nullable=False, comment="创建时间")
    request_id = Column(String(100), nullable=True, comment="请求ID")
    
    # 定义关联关系
    user = relationship("User", backref="model_calls", foreign_keys=[user_id],
                       primaryjoin="foreign(ModelCallLog.user_id) == User.id")
    session = relationship("UserSession", backref="model_calls", foreign_keys=[session_id],
                         primaryjoin="foreign(ModelCallLog.session_id) == UserSession.id")
    
    def __repr__(self):
        return f"<ModelCallLog(id={self.id}, model_name={self.model_name}, created_at={self.created_at})>"


# 系统配置模型
class SystemConfig(Base):
    __tablename__ = "dk_system_configs"
    
    id = Column(String(36), primary_key=True, default=lambda: str(uuid4()))
    config_key = Column(String(100), unique=True, nullable=False, comment="配置键")
    config_value = Column(Text, nullable=False, comment="配置值")
    config_description = Column(Text, nullable=True, comment="配置描述")
    config_group = Column(String(50), nullable=False, default="system", comment="配置分组")
    is_system = Column(Integer, nullable=False, default=0, comment="是否系统配置 0=否，1=是")
    created_at = Column(DateTime, default=datetime.now)
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)
    
    def dict(self):
        return {
            "id": self.id,
            "config_key": self.config_key,
            "config_value": self.config_value,
            "config_description": self.config_description,
            "config_group": self.config_group,
            "is_system": bool(self.is_system),
            "created_at": self.created_at.isoformat() if self.created_at else None,
            "updated_at": self.updated_at.isoformat() if self.updated_at else None
        }


async def create_db_and_tables():
    async with engine.begin() as conn:
        await conn.run_sync(Base.metadata.create_all)

# 新增方法：根据表名删除表


async def drop_table(table_name: str):
    """
    根据表名删除表
    
    参数:
        table_name (str): 要删除的表名 (完整表名，包含前缀)
    """
    async with engine.begin() as conn:
        try:
            await conn.execute(text(f"DROP TABLE IF EXISTS {table_name}"))
            logger.info(f"表 {table_name} 已成功删除")
        except Exception as e:
            logger.error(f"删除表 {table_name} 时出错: {e}")


async def get_async_session() -> AsyncGenerator[AsyncSession, None]:
    async with async_session_maker() as session:
        try:
            yield session
        finally:
            await session.close()


async def get_user_db(session: AsyncSession = Depends(get_async_session)):
    class SQLAlchemyUserDatabaseCustom(SQLAlchemyUserDatabase):
        async def get_by_username(self, username: str) -> Optional[User]:
            """通过用户名查询用户"""
            statement = select(User).where(User.username == username)
            return await self._get_user(statement)

    yield SQLAlchemyUserDatabaseCustom(session, User)


def create_df_table(df, table_name):
    df.to_sql(table_name, con=engine, if_exists='replace', index=False)
    return f"表{table_name}创建成功"


def get_table_structure(table_name):
    """
    获取表的结构信息，包括表名、列名、列类型等，用于传递给大模型
    
    参数:
        table_name (str): 要查询的表名 (完整表名，包含前缀)
        
    返回:
        dict: 包含表结构信息的字典
    """
    try:
        # 创建数据库连接
        engine = create_engine(DATABASETB_URL)
        
        # 创建检查器
        inspector = inspect(engine)
        
        # 检查表是否存在
        if table_name not in inspector.get_table_names():
            return {"error": f"表 {table_name} 不存在"}
        
        # 获取表的列信息
        columns_info = []
        primary_keys = []
        
        # 获取主键信息
        pk_constraint = inspector.get_pk_constraint(table_name)
        if pk_constraint and 'constrained_columns' in pk_constraint:
            primary_keys = pk_constraint['constrained_columns']
        
        # 获取列信息
        columns = inspector.get_columns(table_name)
        for column in columns:
            col_info = {
                "name": column['name'],
                "type": str(column['type']),
                "primary_key": column['name'] in primary_keys,
                "nullable": column.get('nullable', True)
            }
            columns_info.append(col_info)
        
        # 获取样本数据（最多5条）
        with engine.connect() as connection:
            query = text(f"SELECT * FROM {table_name} LIMIT 5")
            result = connection.execute(query)
            sample_data = [dict(row._mapping) for row in result]
        
        # 构建表结构信息
        table_structure = {
            "table_name": table_name,
            "columns": columns_info,
            "primary_keys": primary_keys,
            "sample_data": sample_data
        }
        
        # 尝试识别数值型和日期型列
        numeric_columns = []
        date_columns = []
        text_columns = []
        
        for col in columns_info:
            col_type = col["type"].lower()
            col_name = col["name"]
            
            # 识别数值型列
            if any(num_type in col_type for num_type in ['int', 'float', 'double', 'decimal', 'numeric']):
                numeric_columns.append(col_name)
            # 识别日期型列
            elif any(date_type in col_type for date_type in ['date', 'time', 'timestamp']):
                date_columns.append(col_name)
            # 识别文本型列
            elif any(text_type in col_type for text_type in ['varchar', 'text', 'char', 'clob']):
                text_columns.append(col_name)
        
        # 添加列类型分类
        table_structure["column_types"] = {
            "numeric_columns": numeric_columns,
            "date_columns": date_columns,
            "text_columns": text_columns
        }
        
        # 添加适合大模型的描述信息
        description = f"表 {table_name} 包含 {len(columns_info)} 个字段，其中数值型字段 {len(numeric_columns)} 个，日期型字段 {len(date_columns)} 个，文本型字段 {len(text_columns)} 个。"
        
        if primary_keys:
            description += f" 主键为: {', '.join(primary_keys)}。"
        
        table_structure["description"] = description
        
        # 添加用于生成SQL的提示
        sql_hints = []
        
        # 添加数值型列的聚合函数提示
        if numeric_columns:
            sql_hints.append(f"数值列 ({', '.join(numeric_columns)}) 可以使用SUM, AVG, MAX, MIN等聚合函数")
        
        # 添加日期型列的日期函数提示
        if date_columns:
            sql_hints.append(f"日期列 ({', '.join(date_columns)}) 可以使用STRFTIME函数进行日期格式化和时间周期提取")
        
        # 添加文本型列的文本函数提示
        if text_columns:
            sql_hints.append(f"文本列 ({', '.join(text_columns)}) 可以使用LIKE操作符进行模糊匹配")
        
        table_structure["sql_hints"] = sql_hints
        
        return table_structure
        
    except Exception as e:
        logger.error(f"获取表 {table_name} 结构时出错: {str(e)}")
        return {"error": f"获取表结构失败: {str(e)}"}


# 数据源相关模型
class DataSource(Base):
    """数据源模型，存储数据库连接信息"""
    __tablename__ = "dk_data_sources"

    id = Column(String(36), primary_key=True, default=lambda: str(uuid4()))
    name = Column(String(255), nullable=False, comment="数据源名称")
    description = Column(Text, nullable=True, comment="数据源描述")
    type = Column(String(50), nullable=True, comment="数据库类型：MySQL, SQLServer, PostgreSQL, Oracle")
    
    # 连接信息
    host = Column(String(255), nullable=True, comment="主机地址")
    port = Column(Integer, nullable=True, comment="端口号")
    database = Column(String(255), nullable=True, comment="数据库名称")
    username = Column(String(255), nullable=True, comment="用户名")
    password_encrypted = Column(Text, nullable=True, comment="加密后的密码")
    
    # 安全配置
    use_ssl = Column(Boolean, default=False, comment="是否使用SSL连接")
    ssl_ca = Column(Text, nullable=True, comment="SSL CA证书")
    ssl_cert = Column(Text, nullable=True, comment="SSL客户端证书")
    ssl_key = Column(Text, nullable=True, comment="SSL客户端密钥")
    
    # 状态信息
    created_at = Column(DateTime, default=datetime.now, comment="创建时间")
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now, comment="更新时间")
    last_checked_at = Column(DateTime, nullable=True, comment="最后检查连接时间")
    connection_status = Column(String(50), default="unknown", comment="连接状态：unknown, connected, failed")
    error_message = Column(Text, nullable=True, comment="连接错误信息")
    
    # 关联关系
    datasets = relationship("DataSet", back_populates="data_source", cascade="all, delete-orphan",
                           primaryjoin="DataSource.id == foreign(DataSet.data_source_id)")
    
    def set_password(self, password):
        """设置加密密码"""
        self.password_encrypted = encrypt_text(password)
    
    def get_password(self):
        """获取解密后的密码"""
        return decrypt_text(self.password_encrypted) if self.password_encrypted else None
    
    def get_connection_string(self):
        """获取数据库连接字符串"""
        # 处理特殊的本地数据源ID
        if self.id == '00000000-0000-0000-0000-000000000000':
            # 返回本地SQLite数据库连接字符串
            return DATABASETB_URL
            
        password = self.get_password()
        
        if self.type.lower() == "mysql":
            ssl_args = ""
            if self.use_ssl:
                ssl_args = "?ssl=true"
                if self.ssl_ca:
                    ssl_args += f"&ssl_ca={self.ssl_ca}"
                if self.ssl_cert:
                    ssl_args += f"&ssl_cert={self.ssl_cert}"
                if self.ssl_key:
                    ssl_args += f"&ssl_key={self.ssl_key}"
            return f"mysql+pymysql://{self.username}:{password}@{self.host}:{self.port}/{self.database}{ssl_args}"
        
        elif self.type.lower() == "postgresql":
            return f"postgresql://{self.username}:{password}@{self.host}:{self.port}/{self.database}"
        
        elif self.type.lower() == "sqlserver":
            # 针对SQL Server 2008的特殊处理
            # 使用pyodbc兼容模式创建连接
            conn_params = urllib.parse.quote_plus(
                f"DRIVER={{SQL Server}};"  # 使用更通用的SQL Server驱动
                f"SERVER={self.host},{self.port};"
                f"DATABASE={self.database};"
                f"UID={self.username};"
                f"PWD={password};"
                f"TrustServerCertificate=yes;"
                f"Connection Timeout=30;"
            )
            return f"mssql+pyodbc:///?odbc_connect={conn_params}"
        
        elif self.type.lower() == "oracle":
            return f"oracle+cx_oracle://{self.username}:{password}@{self.host}:{self.port}/{self.database}"
            
        elif self.type.lower() == "sqlite":
            # 对于SQLite，支持内存数据库、文件数据库等
            if self.database == ":memory:":
                return "sqlite:///:memory:"
            else:
                # 如果是文件路径，确保格式正确
                return f"sqlite:///{self.database}"
        
        else:
            raise ValueError(f"不支持的数据库类型: {self.type}")
    
    def dict(self):
        """转换为字典（不包含敏感信息）"""
        return {
            "id": self.id,
            "name": self.name,
            "description": self.description,
            "type": self.type,
            "host": self.host,
            "port": self.port,
            "database": self.database,
            "username": self.username,
            "use_ssl": self.use_ssl,
            "created_at": self.created_at.isoformat() if self.created_at else None,
            "updated_at": self.updated_at.isoformat() if self.updated_at else None,
            "last_checked_at": self.last_checked_at.isoformat() if self.last_checked_at else None,
            "connection_status": self.connection_status,
            "datasets_count": len(self.datasets) if self.datasets else 0
        }
        
    def to_dict(self, include_relationships=False):
        """转换为字典（不访问关联关系）"""
        result = {
            "id": self.id,
            "name": self.name,
            "description": self.description,
            "type": self.type,
            "host": self.host,
            "port": self.port,
            "database": self.database,
            "username": self.username,
            "use_ssl": self.use_ssl,
            "created_at": self.created_at.isoformat() if self.created_at else None,
            "updated_at": self.updated_at.isoformat() if self.updated_at else None,
            "last_checked_at": self.last_checked_at.isoformat() if self.last_checked_at else None,
            "connection_status": self.connection_status
        }
        
        # 只有当显式要求且关系已预加载时，才包含关系数据
        if include_relationships and hasattr(self, 'datasets') and self.datasets is not None:
            result["datasets_count"] = len(self.datasets)
            
        return result


class DataSet(Base):
    """数据集模型，存储数据集配置"""
    __tablename__ = "dk_data_sets"

    id = Column(String(36), primary_key=True, default=lambda: str(uuid4()))
    data_source_id = Column(String(36), nullable=False)
    
    # 基本信息ho
    name = Column(String(255), nullable=False, comment="数据集名称（系统中显示的名称）")
    description = Column(Text, nullable=True, comment="数据集描述")
    original_name = Column(String(255), nullable=True, comment="原始表名或视图名")
    type = Column(String(50), nullable=True, comment="数据集类型：table, view, query")
    custom_query = Column(Text, nullable=True, comment="自定义查询SQL")
    category = Column(String(255), nullable=True, comment="分类")
    tags = Column(Text, nullable=True, comment="标签，JSON格式的标签数组")
    user_id = Column(String(36), nullable=True, comment="创建人ID")
    
    # 同步配置
    sync_enabled = Column(Boolean, default=True, comment="是否启用同步")
    sync_frequency = Column(String(50), default="daily", comment="同步频率：manual, hourly, daily, weekly")
    sync_type = Column(String(50), default="full", comment="同步类型：full, incremental")
    incremental_field = Column(String(255), nullable=True, comment="增量同步字段")
    last_sync_value = Column(String(255), nullable=True, comment="上次增量同步值")
    
    # 状态信息
    created_at = Column(DateTime, default=datetime.now, comment="创建时间")
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now, comment="更新时间")
    last_synced_at = Column(DateTime, nullable=True, comment="最后同步时间")
    last_structure_changed_at = Column(DateTime, nullable=True, comment="最后结构变更时间")
    sync_status = Column(String(50), default="pending", comment="同步状态：pending, syncing, completed, failed, structure_changed")
    error_message = Column(Text, nullable=True, comment="同步错误信息")
    
    # 结构变更处理
    structure_change_strategy = Column(String(50), default="notify", comment="结构变更处理策略：notify, auto_apply, ignore")
    
    # 新增字段
    cleaning_log = Column(Text, nullable=True, comment="清洗日志，记录数据清洗过程和结果")
    data_summary_log = Column(Text, nullable=True, comment="数据摘要日志，记录数据统计摘要信息")
    dataset_summary = Column(Text, nullable=True, comment="数据集摘要，对数据集的整体摘要和说明")
    
    # 关联关系
    data_source = relationship("DataSource", back_populates="datasets", foreign_keys=[data_source_id], 
                              primaryjoin="foreign(DataSet.data_source_id) == DataSource.id")
    fields = relationship("DataField", back_populates="dataset", cascade="all, delete-orphan",
                         primaryjoin="DataSet.id == foreign(DataField.dataset_id)")
    role_accesses = relationship(
        "RoleDatasetAccess", 
        back_populates="dataset", 
        cascade="all, delete-orphan",
        primaryjoin="DataSet.id == foreign(RoleDatasetAccess.dataset_id)"
    )
    
    def get_tags_list(self):
        """获取标签列表"""
        if not self.tags:
            return []
        try:
            return json.loads(self.tags)
        except:
            return []
    
    def set_tags_list(self, tags_list):
        """设置标签列表"""
        if tags_list is None:
            self.tags = None
        else:
            self.tags = json.dumps(tags_list)
    
    def dict(self):
        """转换为字典（保持向后兼容，但避免懒加载问题）"""
        # 简单地调用to_dict方法，保持最接近原始行为的参数
        return self.to_dict(include_relationships=True)
        
    def to_dict(self, include_relationships=False):
        """转换为字典（避免懒加载）"""
        result = {
            "id": self.id,
            "data_source_id": self.data_source_id,
            "name": self.name,
            "description": self.description,
            "original_name": self.original_name,
            "type": self.type,
            "custom_query": self.custom_query,
            "category": self.category,
            "tags": self.get_tags_list(),
            "user_id": self.user_id,
            "sync_enabled": self.sync_enabled,
            "sync_frequency": self.sync_frequency,
            "sync_type": self.sync_type,
            "incremental_field": self.incremental_field,
            "last_sync_value": self.last_sync_value,
            "created_at": self.created_at.isoformat() if self.created_at else None,
            "updated_at": self.updated_at.isoformat() if self.updated_at else None,
            "last_synced_at": self.last_synced_at.isoformat() if self.last_synced_at else None,
            "last_structure_changed_at": self.last_structure_changed_at.isoformat() if self.last_structure_changed_at else None,
            "sync_status": self.sync_status,
            "error_message": self.error_message,
            "structure_change_strategy": self.structure_change_strategy,
            "cleaning_log": self.cleaning_log,
            "data_summary_log": self.data_summary_log,
            "dataset_summary": self.dataset_summary
        }
        
        # 只有当显式要求且关系已预加载时才包含关系数据
        if include_relationships and hasattr(self, 'fields') and self.fields is not None:
            result["fields_count"] = len(self.fields)
            
        return result


class DataField(Base):
    """数据字段模型，存储字段元数据"""
    __tablename__ = "dk_data_fields"

    id = Column(String(36), primary_key=True, default=lambda: str(uuid4()))
    dataset_id = Column(String(36), nullable=False)
    
    # 基本信息
    name = Column(String(255), nullable=False, comment="字段名称")
    display_name = Column(String(255), nullable=True, comment="显示名称")
    description = Column(Text, nullable=True, comment="字段描述")
    data_type = Column(String(50), nullable=False, comment="字段数据类型")
    original_data_type = Column(String(50), nullable=False, comment="原始数据类型（数据库中的类型）")
    
    # 特性
    is_primary_key = Column(Boolean, default=False, comment="是否为主键")
    is_nullable = Column(Boolean, default=True, comment="是否可为空")
    visible = Column(String(1), default="1", nullable=True, comment="是否可见 1=可见，0=不可见")
    
    # 描述信息
    synonyms = Column(Text, nullable=True, comment="同义词列表，JSON格式")
    business_terms = Column(Text, nullable=True, comment="业务术语映射，JSON格式")
    semantic_type = Column(String(50), nullable=True, comment="语义类型，如人名、地址、电话等")
    
    # 顺序
    ordinal_position = Column(Integer, nullable=False, comment="字段顺序")
    
    # 时间信息
    created_at = Column(DateTime, default=datetime.now, comment="创建时间")
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now, comment="更新时间")
    
    # 关联关系
    dataset = relationship("DataSet", back_populates="fields", foreign_keys=[dataset_id], 
                           primaryjoin="foreign(DataField.dataset_id) == DataSet.id")
    change_logs = relationship("FieldChangeLog", back_populates="field", cascade="all, delete-orphan", 
                              foreign_keys="[FieldChangeLog.field_id]", 
                              primaryjoin="DataField.id == foreign(FieldChangeLog.field_id)")
    
    def get_synonyms_list(self):
        """获取同义词列表"""
        if not self.synonyms:
            return []
        try:
            return json.loads(self.synonyms)
        except:
            return []
    
    def set_synonyms_list(self, synonyms_list):
        """设置同义词列表"""
        if synonyms_list is None:
            self.synonyms = None
        else:
            self.synonyms = json.dumps(synonyms_list)
    
    def get_business_terms(self):
        """获取业务术语映射"""
        if not self.business_terms:
            return {}
        try:
            return json.loads(self.business_terms)
        except:
            return {}
    
    def set_business_terms(self, terms_dict):
        """设置业务术语映射"""
        if terms_dict is None:
            self.business_terms = None
        else:
            self.business_terms = json.dumps(terms_dict)
    
    def dict(self):
        """转换为字典"""
        return {
            "id": self.id,
            "dataset_id": self.dataset_id,
            "name": self.name,
            "display_name": self.display_name,
            "description": self.description,
            "data_type": self.data_type,
            "original_data_type": self.original_data_type,
            "is_primary_key": self.is_primary_key,
            "is_nullable": self.is_nullable,
            "visible": self.visible,
            "synonyms": self.get_synonyms_list(),
            "business_terms": self.get_business_terms(),
            "semantic_type": self.semantic_type,
            "ordinal_position": self.ordinal_position,
            "created_at": self.created_at.isoformat() if self.created_at else None,
            "updated_at": self.updated_at.isoformat() if self.updated_at else None
        }

    def to_dict(self, include_relationships=False):
        """转换为字典（避免懒加载）"""
        # 调用原有的dict方法获取基本数据
        result = self.dict()
        
        # 只有当显式要求且关系已预加载时才包含关系数据
        if include_relationships:
            if hasattr(self, 'dataset') and self.dataset is not None:
                result["dataset"] = self.dataset.dict() if hasattr(self.dataset, 'dict') else None
                
        return result


class FieldChangeLog(Base):
    """字段变更日志，记录字段变更历史"""
    __tablename__ = "dk_field_change_logs"
    
    id = Column(String(36), primary_key=True, default=lambda: str(uuid4()))
    field_id = Column(String(36), nullable=True)
    dataset_id = Column(String(36), nullable=False)
    
    # 变更信息
    change_type = Column(String(50), nullable=False, comment="变更类型：added, removed, type_changed, property_changed")
    field_name = Column(String(255), nullable=False, comment="字段名称")
    change_details = Column(Text, nullable=False, comment="变更详情，JSON格式")
    old_value = Column(Text, nullable=True, comment="变更前的值")
    new_value = Column(Text, nullable=True, comment="变更后的值")
    
    # 状态信息
    status = Column(String(50), default="pending", comment="状态：pending, approved, rejected, applied")
    applied_at = Column(DateTime, nullable=True, comment="应用时间")
    
    # 时间和用户信息
    created_at = Column(DateTime, default=datetime.now, comment="创建时间")
    created_by = Column(String(36), nullable=True, comment="创建用户ID")
    
    # 关联关系
    field = relationship("DataField", back_populates="change_logs", foreign_keys=[field_id], 
                         primaryjoin="foreign(FieldChangeLog.field_id) == DataField.id")
    dataset = relationship("DataSet", foreign_keys=[dataset_id], 
                           primaryjoin="foreign(FieldChangeLog.dataset_id) == DataSet.id")
    
    def get_change_details(self):
        """获取变更详情"""
        if not self.change_details:
            return {}
        try:
            return json.loads(self.change_details)
        except:
            return {}
    
    def set_change_details(self, details_dict):
        """设置变更详情"""
        if details_dict is None:
            self.change_details = None
        else:
            self.change_details = json.dumps(details_dict)
    
    def dict(self):
        """转换为字典"""
        return {
            "id": self.id,
            "field_id": self.field_id,
            "dataset_id": self.dataset_id,
            "change_type": self.change_type,
            "field_name": self.field_name,
            "change_details": self.get_change_details(),
            "old_value": self.old_value,
            "new_value": self.new_value,
            "status": self.status,
            "applied_at": self.applied_at.isoformat() if self.applied_at else None,
            "created_at": self.created_at.isoformat() if self.created_at else None,
            "created_by": self.created_by
        }

    def to_dict(self, include_relationships=False):
        """转换为字典（避免懒加载）"""
        # 调用原有的dict方法获取基本数据
        result = self.dict()
        
        # 只有当显式要求且关系已预加载时才包含关系数据
        if include_relationships:
            if hasattr(self, 'field') and self.field is not None:
                result["field"] = self.field.dict() if hasattr(self.field, 'dict') else None
            if hasattr(self, 'dataset') and self.dataset is not None:
                result["dataset"] = self.dataset.dict() if hasattr(self.dataset, 'dict') else None
                
        return result


class RoleDatasetAccess(Base):
    """角色数据集访问权限表"""
    __tablename__ = "dk_role_dataset_access"
    
    id = Column(String(36), primary_key=True, default=lambda: str(uuid4()))
    role_id = Column(String(36), nullable=False, comment="角色ID")  # 移除外键约束
    dataset_id = Column(String(36), nullable=False, comment="数据集ID")  # 移除外键约束
    can_export = Column(Boolean, default=False, comment="是否可以导出数据")
    created_at = Column(DateTime, default=datetime.now, comment="创建时间")
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now, comment="更新时间")
    expires_at = Column(DateTime, nullable=True, comment="过期时间，null表示永不过期")
    
    # 关系定义（不使用外键约束，但保留关系）
    role = relationship("Role", foreign_keys=[role_id], 
                       primaryjoin="RoleDatasetAccess.role_id == foreign(Role.id)")
    dataset = relationship("DataSet", foreign_keys=[dataset_id],
                          primaryjoin="RoleDatasetAccess.dataset_id == foreign(DataSet.id)")
    
    def dict(self):
        return {
            "id": self.id,
            "role_id": self.role_id,
            "dataset_id": self.dataset_id,
            "can_export": self.can_export,
            "created_at": self.created_at.isoformat() if self.created_at else None,
            "updated_at": self.updated_at.isoformat() if self.updated_at else None,
            "expires_at": self.expires_at.isoformat() if self.expires_at else None,
        }

    def is_expired(self):
        """检查权限是否过期"""
        if self.expires_at is None:
            return False
        return datetime.now() > self.expires_at


class TaskConfig(Base):
    """任务配置表 - 存储动态可配置的任务类型"""
    __tablename__ = "dk_task_config"
    
    id = Column(String(36), primary_key=True, default=lambda: str(uuid4()))
    task_type = Column(String(50), nullable=False, index=True)  # 'api_function', 'data_query'
    task_name = Column(String(100), nullable=False)
    description = Column(Text)
    params_description = Column(Text, comment="参数说明，描述任务所需的参数信息，用于AI意图识别")
    is_active = Column(Boolean, default=True)
    priority = Column(Integer, default=0)  # 优先级，越高越优先
    config_schema = Column(MEDIUMTEXT)  # 任务配置的JSON Schema
    created_at = Column(DateTime, default=datetime.now)
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)
    
    # 关联关系
    api_functions = relationship("ApiFunction", back_populates="task_config", cascade="all, delete-orphan",
                                primaryjoin="TaskConfig.id == foreign(ApiFunction.task_config_id)")
    



class ApiFunction(Base):
    """API函数配置表 - 存储外部系统API信息（如教务系统查成绩API）"""
    __tablename__ = "dk_api_function"
    
    id = Column(String(36), primary_key=True, default=lambda: str(uuid4()))
    task_config_id = Column(String(36), ForeignKey("dk_task_config.id"))
    function_name = Column(String(100), nullable=False, index=True)
    description = Column(Text)
    endpoint_url = Column(String(500), nullable=False)
    http_method = Column(String(10), default="POST")
    headers = Column(MEDIUMTEXT)  # 请求头配置
    auth_config = Column(MEDIUMTEXT)  # 认证配置
    parameters_schema = Column(MEDIUMTEXT)  # 参数Schema（用于验证必需参数）
    response_schema = Column(MEDIUMTEXT)  # 响应Schema
    timeout = Column(Integer, default=30)
    retry_times = Column(Integer, default=3)
    is_active = Column(Boolean, default=True)
    # 意图识别配置
    intent_keywords = Column(MEDIUMTEXT)  # 关键词列表，用于意图识别
    intent_patterns = Column(MEDIUMTEXT)  # 正则表达式模式
    intent_examples = Column(MEDIUMTEXT)  # 意图示例
    required_params = Column(MEDIUMTEXT)  # 必需参数列表
    created_at = Column(DateTime, default=datetime.now)
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)
    
    # 关联关系
    task_config = relationship("TaskConfig", back_populates="api_functions", foreign_keys=[task_config_id],
                              primaryjoin="foreign(ApiFunction.task_config_id) == TaskConfig.id")
    


class Task(Base):
    """任务表 - 存储任务定义和执行结果（合并表设计）"""
    __tablename__ = "dk_tasks"
    
    id = Column(String(36), primary_key=True, default=lambda: str(uuid4()))
    
    # 基本信息
    task_name = Column(String(255), nullable=False, comment="任务名称")
    task_type = Column(String(50), nullable=False, index=True, comment="任务类型：api_function, data_query")
    task_description = Column(Text, nullable=True, comment="任务描述")
    priority = Column(Integer, default=0, comment="任务优先级，数字越大优先级越高")
    
    # 关联信息
    user_id = Column(String(36), nullable=False, comment="创建用户ID")
    session_id = Column(String(36), nullable=True, comment="关联的会话ID")
    intent_id = Column(String(36), nullable=True, comment="意图识别ID，用于关联意图识别结果")
    parent_task_id = Column(String(36), nullable=True, comment="父任务ID，用于任务依赖关系")
    messageid = Column(String(36), nullable=True, comment="关联的系统回复消息ID")
    
    # 任务配置
    task_config_id = Column(String(36), nullable=True, comment="关联的任务配置ID")
    api_function_id = Column(String(36), nullable=True, comment="关联的API函数ID")
    dataset_id = Column(String(36), nullable=True, comment="关联的数据集ID")
    
    # 任务参数和配置
    input_parameters = Column(MEDIUMTEXT, nullable=True, comment="任务输入参数，JSON格式")
    task_config_json = Column(MEDIUMTEXT, nullable=True, comment="任务配置信息，JSON格式")
    generated_sql = Column(Text, nullable=True, comment="AI生成的SQL语句")
    
    # 执行状态
    status = Column(String(50), nullable=False, default="pending", index=True, 
                   comment="任务状态：pending=待执行, running=执行中, completed=已完成, failed=失败, cancelled=已取消")
    progress = Column(Integer, default=0, comment="执行进度百分比 0-100")
    
    # 时间信息
    created_at = Column(DateTime, default=datetime.now, comment="创建时间")
    started_at = Column(DateTime, nullable=True, comment="开始执行时间")
    completed_at = Column(DateTime, nullable=True, comment="完成时间")
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now, comment="更新时间")
    
    # 执行信息
    execution_order = Column(Integer, default=1, comment="执行顺序")
    estimated_duration = Column(Integer, nullable=True, comment="预计执行时长（秒）")
    
    # === 执行结果字段（从TaskExecutionResult合并过来） ===
    
    # 执行结果基本信息
    execution_status = Column(String(50), nullable=True, 
                             comment="执行状态：success=成功, error=错误, timeout=超时, cancelled=取消")
    result_type = Column(String(50), nullable=True, 
                        comment="结果类型：api_result, query_result, chart_result, report_result")
    duration_ms = Column(Integer, nullable=True, comment="执行耗时（毫秒）")
    
    # 结果数据
    result_data = Column(MEDIUMTEXT, nullable=True, comment="执行结果数据，JSON格式")
    result_summary = Column(Text, nullable=True, comment="结果摘要")
    error_message = Column(Text, nullable=True, comment="错误信息")
    error_details = Column(MEDIUMTEXT, nullable=True, comment="详细错误信息，JSON格式")
    
    # 数据查询相关结果
    sql_query = Column(Text, nullable=True, comment="实际执行的SQL语句")
    rows_affected = Column(Integer, nullable=True, comment="影响的行数")
    columns_info = Column(MEDIUMTEXT, nullable=True, comment="列信息，JSON格式")
    
    # API调用相关结果
    api_endpoint = Column(String(500), nullable=True, comment="调用的API端点")
    http_status_code = Column(Integer, nullable=True, comment="HTTP状态码")
    response_size = Column(Integer, nullable=True, comment="响应大小（字节）")
    
    # 文件和资源相关
    output_files = Column(MEDIUMTEXT, nullable=True, comment="输出文件列表，JSON格式")
    resource_usage = Column(MEDIUMTEXT, nullable=True, comment="资源使用情况，JSON格式")
    
    # 元数据
    execution_environment = Column(String(100), nullable=True, comment="执行环境信息")
    execution_node = Column(String(100), nullable=True, comment="执行节点")
    
    # 关联关系
    user = relationship("User", backref="tasks", foreign_keys=[user_id],
                       primaryjoin="foreign(Task.user_id) == User.id")
    session = relationship("UserSession", backref="tasks", foreign_keys=[session_id],
                          primaryjoin="foreign(Task.session_id) == UserSession.id")
    task_config = relationship("TaskConfig", backref="tasks", foreign_keys=[task_config_id],
                              primaryjoin="foreign(Task.task_config_id) == TaskConfig.id")
    api_function = relationship("ApiFunction", backref="tasks", foreign_keys=[api_function_id],
                               primaryjoin="foreign(Task.api_function_id) == ApiFunction.id")
    dataset = relationship("DataSet", backref="tasks", foreign_keys=[dataset_id],
                          primaryjoin="foreign(Task.dataset_id) == DataSet.id")
    parent_task = relationship("Task", remote_side=[id], foreign_keys=[parent_task_id],
                              primaryjoin="foreign(Task.parent_task_id) == Task.id")
    
    # 一对多关系
    child_tasks = relationship("Task", back_populates="parent_task", 
                              primaryjoin="Task.id == foreign(Task.parent_task_id)")
    
    # JSON字段处理方法
    def get_input_parameters(self):
        """获取输入参数"""
        if not self.input_parameters:
            return {}
        try:
            return json.loads(self.input_parameters)
        except:
            return {}
    
    def set_input_parameters(self, params_dict):
        """设置输入参数"""
        if params_dict is None:
            self.input_parameters = None
        else:
            self.input_parameters = json.dumps(params_dict, ensure_ascii=False, default=self._json_serializer)
    
    def get_task_config_json(self):
        """获取任务配置"""
        if not self.task_config_json:
            return {}
        try:
            return json.loads(self.task_config_json)
        except:
            return {}
    
    def set_task_config_json(self, config_dict):
        """设置任务配置"""
        if config_dict is None:
            self.task_config_json = None
        else:
            self.task_config_json = json.dumps(config_dict, ensure_ascii=False, default=self._json_serializer)
    
    def get_result_data(self):
        """获取结果数据"""
        if not self.result_data:
            return {}
        try:
            return json.loads(self.result_data)
        except:
            return {}
    
    def set_result_data(self, data_dict):
        """设置结果数据"""
        if data_dict is None:
            self.result_data = None
        else:
            self.result_data = json.dumps(data_dict, ensure_ascii=False, default=self._json_serializer)
    
    def get_error_details(self):
        """获取详细错误信息"""
        if not self.error_details:
            return {}
        try:
            return json.loads(self.error_details)
        except:
            return {}
    
    def set_error_details(self, error_dict):
        """设置详细错误信息"""
        if error_dict is None:
            self.error_details = None
        else:
            self.error_details = json.dumps(error_dict, ensure_ascii=False, default=self._json_serializer)
    
    def get_columns_info(self):
        """获取列信息"""
        if not self.columns_info:
            return []
        try:
            return json.loads(self.columns_info)
        except:
            return []
    
    def set_columns_info(self, columns_list):
        """设置列信息"""
        if columns_list is None:
            self.columns_info = None
        else:
            self.columns_info = json.dumps(columns_list, ensure_ascii=False, default=self._json_serializer)
    
    def get_output_files(self):
        """获取输出文件列表"""
        if not self.output_files:
            return []
        try:
            return json.loads(self.output_files)
        except:
            return []
    
    def set_output_files(self, files_list):
        """设置输出文件列表"""
        if files_list is None:
            self.output_files = None
        else:
            self.output_files = json.dumps(files_list, ensure_ascii=False, default=self._json_serializer)
    
    def get_resource_usage(self):
        """获取资源使用情况"""
        if not self.resource_usage:
            return {}
        try:
            return json.loads(self.resource_usage)
        except:
            return {}
    
    def set_resource_usage(self, usage_dict):
        """设置资源使用情况"""
        if usage_dict is None:
            self.resource_usage = None
        else:
            self.resource_usage = json.dumps(usage_dict, ensure_ascii=False, default=self._json_serializer)
    
    @staticmethod
    def _json_serializer(obj):
        """JSON序列化器，处理特殊类型"""
        from decimal import Decimal
        from datetime import datetime, date
        
        if isinstance(obj, Decimal):
            # 将Decimal转换为float
            return float(obj)
        elif isinstance(obj, (datetime, date)):
            # 将日期时间转换为ISO格式字符串
            return obj.isoformat()
        else:
            # 对于其他不可序列化的类型，转换为字符串
            return str(obj)
    
    # 状态检查方法
    def is_completed(self):
        """检查任务是否已完成"""
        return self.status in ["completed", "failed", "cancelled"]
    
    def is_running(self):
        """检查任务是否正在执行"""
        return self.status == "running"
    
    def is_successful(self):
        """检查执行是否成功"""
        return self.execution_status == "success"
    
    def get_duration_seconds(self):
        """获取执行时长（秒）"""
        if self.duration_ms is None:
            return None
        return self.duration_ms / 1000.0
    
    def dict(self):
        """转换为字典"""
        return {
            "id": self.id,
            "task_name": self.task_name,
            "task_type": self.task_type,
            "task_description": self.task_description,
            "priority": self.priority,
            "user_id": self.user_id,
            "session_id": self.session_id,
            "intent_id": self.intent_id,
            "parent_task_id": self.parent_task_id,
            "messageid": self.messageid,
            "task_config_id": self.task_config_id,
            "api_function_id": self.api_function_id,
            "dataset_id": self.dataset_id,
            "input_parameters": self.get_input_parameters(),
            "task_config": self.get_task_config_json(),
            "generated_sql": self.generated_sql,
            "status": self.status,
            "progress": self.progress,
            "created_at": self.created_at.isoformat() if self.created_at else None,
            "started_at": self.started_at.isoformat() if self.started_at else None,
            "completed_at": self.completed_at.isoformat() if self.completed_at else None,
            "updated_at": self.updated_at.isoformat() if self.updated_at else None,
            "execution_order": self.execution_order,
            "estimated_duration": self.estimated_duration,
            # 执行结果相关字段
            "execution_status": self.execution_status,
            "result_type": self.result_type,
            "duration_ms": self.duration_ms,
            "duration_seconds": self.get_duration_seconds(),
            "result_data": self.get_result_data(),
            "result_summary": self.result_summary,
            "error_message": self.error_message,
            "error_details": self.get_error_details(),
            "sql_query": self.sql_query,
            "rows_affected": self.rows_affected,
            "columns_info": self.get_columns_info(),
            "api_endpoint": self.api_endpoint,
            "http_status_code": self.http_status_code,
            "response_size": self.response_size,
            "output_files": self.get_output_files(),
            "resource_usage": self.get_resource_usage(),
            "execution_environment": self.execution_environment,
            "execution_node": self.execution_node
        }


class KnowledgeBase(Base):
    """知识库模型"""
    __tablename__ = "dk_knowledge_base"

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(100), nullable=False, comment="知识库名称")
    description = Column(Text, nullable=True, comment="知识库描述")
    category = Column(String(50), nullable=True, default="general", comment="知识库分类")
    status = Column(Integer, default=1, comment="0=禁用,1=启用")
    search_weight = Column(types.DECIMAL(3, 2), default=1.00, comment="搜索权重(0.1-2.0)")
    access_level = Column(String(20), default="public", comment="访问级别: public, role_based")
    allowed_roles = Column(JSON, nullable=True, comment="允许访问的角色列表(JSON)")
    created_by = Column(String(36), nullable=True, comment="创建者用户ID")
    created_at = Column(DateTime, default=datetime.now)
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)

    def to_dict(self):
        return {
            "id": self.id,
            "name": self.name,
            "description": self.description,
            "category": self.category,
            "status": self.status,
            "search_weight": float(self.search_weight) if self.search_weight is not None else None,
            "access_level": self.access_level,
            "allowed_roles": self.allowed_roles,
            "created_by": self.created_by,
            "created_at": self.created_at.isoformat() if self.created_at else None,
            "updated_at": self.updated_at.isoformat() if self.updated_at else None,
        }


class KnowledgeItem(Base):
    """知识条目模型"""
    __tablename__ = "dk_knowledge_items"

    id = Column(Integer, primary_key=True, autoincrement=True)
    knowledge_base_id = Column(Integer, nullable=False, index=True, comment="所属知识库ID")
    title = Column(String(200), nullable=False, comment="知识标题")
    content = Column(LONGTEXT, nullable=False, comment="知识内容")
    summary = Column(Text, nullable=True, comment="内容摘要")
    keywords = Column(JSON, nullable=True, comment="关键词列表(JSON)")
    tags = Column(JSON, nullable=True, comment="标签列表(JSON)")
    source_type = Column(String(20), default="manual", comment="来源类型: manual/import/api")
    source_url = Column(String(500), nullable=True, comment="来源链接")
    priority = Column(Integer, default=5, comment="优先级(1-10)")
    view_count = Column(Integer, default=0, comment="查看次数")
    useful_count = Column(Integer, default=0, comment="有用评价次数")
    status = Column(Integer, default=1, comment="0=禁用,1=启用,2=待审核")
    created_by = Column(String(36), nullable=True, comment="创建者用户ID")
    created_at = Column(DateTime, default=datetime.now)
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)

    def to_dict(self):
        return {
            "id": self.id,
            "knowledge_base_id": self.knowledge_base_id,
            "title": self.title,
            "content": self.content,
            "summary": self.summary,
            "keywords": self.keywords,
            "tags": self.tags,
            "source_type": self.source_type,
            "source_url": self.source_url,
            "priority": self.priority,
            "view_count": self.view_count,
            "useful_count": self.useful_count,
            "status": self.status,
            "created_by": self.created_by,
            "created_at": self.created_at.isoformat() if self.created_at else None,
            "updated_at": self.updated_at.isoformat() if self.updated_at else None,
        }

